
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <strings.h>
#include <sqlite3.h>
#include <sys/types.h> /* See NOTES */
#include <sys/socket.h>
#include <arpa/inet.h>
#include <unistd.h>
#define PORT 7777
#define ADDRESS "192.168.7.244"
int select_callback(void *data, int argc, char **value, char **name);
int main()
{
    int cntl_flag = 0, id = 0, login_flag = 0, sqlite3_ret = 0, flag = 0;
    int sockfd = -1, connectfd = -1;
    char buf[20] = " ", name[20] = " ", password[20] = " ", result[20] = " ", recv_name[20] = " ", recv_password[20] = " ", sql[200] = " ";
    char *err_msg;
    sqlite3 *db;
    sockfd = socket(AF_INET, SOCK_STREAM, 0);
    sqlite3_ret = sqlite3_open("my.db", &db);
    flag = 0;
    if (sqlite3_ret != SQLITE_OK)
    {
        printf("打开数据库错误 错误码： %s\n", sqlite3_errstr(sqlite3_ret));
        return -1;
    }
    printf("打开数据库完成\n");

    sqlite3_ret = sqlite3_exec(db, "create table student(name char, password char);", NULL, NULL, &err_msg);
    if (sqlite3_ret != SQLITE_OK)
    {
        printf("创建数据库失败，由于-- %s\n", err_msg);
    }
    if (-1 == sockfd)
    {
        perror("socket");
        return -1;
    }
    struct sockaddr_in addr;
    addr.sin_family = AF_INET;
    addr.sin_port = htons(PORT);
    addr.sin_addr.s_addr = inet_addr(ADDRESS);
    if (-1 == bind(sockfd, (struct sockaddr *)&addr, sizeof(addr)))
    {
        perror("bind");
    }
LINK:
    if (-1 == listen(sockfd, 3))
    {
        perror("listen");
        return -1;
    }

    printf("等待客户端连接\n");

    connectfd = accept(sockfd, NULL, NULL);
    if (-1 == connectfd)
    {
        perror("accept");
        return -1;
    }
    printf("连接成功\n");
        
        printf("connectfd -- %d\n",connectfd);
    while (1)
    {
        if (connectfd == -1) // 无连接状态
        {
            goto LINK;
        }
        else
        {
            sprintf(buf, " ");
            read(connectfd, buf, sizeof(buf));
            if (strcmp(buf, "login") == 0) // 登录
            {
                printf("客户端指令--%s\n", buf);
                read(connectfd, name, sizeof(name));
                printf("%s",name);
                sprintf(sql, "select * from student where name='%s';", name);
                read(connectfd, password, sizeof(password));
                flag = 0;
                sqlite3_ret = sqlite3_exec(db, sql, select_callback, (void *)&flag, &err_msg);
                if (sqlite3_ret != SQLITE_OK)
                {
                    printf("查询错误！-- %s\n", err_msg);
                    return -1;
                }
                if (flag == 0)
                {
                    printf("查询用户不存在\n");
                    strcpy(result, "502");
                    write(connectfd, result, sizeof(result));
                }
                else
                {
                    printf("用户存在\n");
                    sprintf(sql, "select * from student where name='%s' and password='%s';", name, password);
                    flag = 0;
                    sqlite3_ret = sqlite3_exec(db, sql, select_callback, (void *)&flag, &err_msg);
                    if (flag == 0)
                    {
                        printf("查询密码不正确\n");
                        strcpy(result, "501");
                        write(connectfd, result, sizeof(result));
                    }
                    else
                    {
                        printf("查询密码正确\n");
                        strcpy(result, "200");
                        write(connectfd, result, sizeof(result));
                    }
                }
            }
            else if (strcmp(buf, "register") == 0) // 注册
            {
                printf("客户端指令--%s\n", buf);
                read(connectfd, name, sizeof(name));
                printf("查询用户名是否已存在\n");
                sprintf(sql, "select * from student where name='%s';", name);
                read(connectfd, password, sizeof(password));
                flag = 0;
                sqlite3_ret = sqlite3_exec(db, sql, select_callback, (void *)&flag, &err_msg);

                if (sqlite3_ret != SQLITE_OK)
                {
                    printf("查询错误！-- %s\n", err_msg);
                    return -1;
                }
                if (flag == 0)
                {
                    printf("查询用户不存在\n");
                    printf("执行注册操作\n");
                    sprintf(sql, "insert into student values('%s', '%s');", name, password);
                    flag = 0;
                    sqlite3_ret = sqlite3_exec(db, sql, select_callback, (void *)&flag, &err_msg);
                    if (sqlite3_ret != SQLITE_OK)
                    {
                        printf("插入失败！-- %s\n", err_msg);
                        strcpy(result, "501");
                        write(connectfd, result, sizeof(result));
                    }
                    else
                    {
                        printf("插入成功\n");
                        strcpy(result, "200");
                        printf("name:%s,passwd:%s",name,password);
                        write(connectfd, result, sizeof(result));
                    }
                }
                else
                {
                    printf("用户名已存在\n");
                    strcpy(result, "501");
                    printf("%s", result);
                    write(connectfd, result, sizeof(result));
                }
            }
            else if (strcmp(buf, "change") == 0) // 修改
            {
                printf("客户端指令--%s\n", buf);
                read(connectfd, name, sizeof(name));

                printf("查询用户密码是否正确\n");

                read(connectfd, password, sizeof(password));
                sprintf(sql, "select * from student where name='%s' and password='%s';", name, password);
                flag = 0;
                sqlite3_ret = sqlite3_exec(db, sql, select_callback, (void *)&flag, &err_msg);
                if (sqlite3_ret != SQLITE_OK)
                {
                    printf("查询错误！-- %s\n", err_msg);
                    return -1;
                }
                if (flag == 0)
                {
                    printf("用户原密码错误\n");
                    strcpy(result, "501");
                    write(connectfd, result, sizeof(result));
                }
                else
                {
                    strcpy(result, "200");
                    write(connectfd, result, sizeof(result));
                    printf("查询用户正确\n");
                    read(connectfd, password, sizeof(password));
                    printf("执行修改操作\n");
                    sprintf(sql, "update student set password='%s'where name ='%s';", password, name);
                    sqlite3_ret = sqlite3_exec(db, sql, select_callback, NULL, &err_msg);
                    if (sqlite3_ret != SQLITE_OK)
                    {
                        printf("修改失败！-- %s\n", err_msg);
                        strcpy(result, "501");
                        write(connectfd, result, sizeof(result));
                    }
                    else
                    {
                        printf("修改成功\n");
                        strcpy(result, "200");
                        write(connectfd, result, sizeof(result));
                    }
                }
            }
            else if (strcmp(buf, "remove") == 0) // 删除
            {
                printf("客户端指令--%s\n", buf);
                read(connectfd, name, sizeof(name));

                printf("查询用户密码是否正确\n");
                read(connectfd, password, sizeof(password));
                sprintf(sql, "select * from student where name='%s' and password='%s';", name, password);
                flag = 0;
                sqlite3_ret = sqlite3_exec(db, sql, select_callback, (void *)&flag, &err_msg);
                if (sqlite3_ret != SQLITE_OK)
                {
                    printf("查询错误！-- %s\n", err_msg);
                    return -1;
                }
                if (flag == 0)
                {
                    printf("用户原密码错误\n");
                    strcpy(result, "501");
                    write(connectfd, result, sizeof(result));
                }
                else
                {
                    printf("查询用户正确\n");
                    printf("执行删除操作\n");
                    sprintf(sql, "delete from student where name = '%s';", name);
                    flag = 0;
                    sqlite3_ret = sqlite3_exec(db, sql, select_callback, (void *)&flag, &err_msg);
                    if (sqlite3_ret != SQLITE_OK)
                    {
                        printf("删除失败！-- %s\n", err_msg);
                        strcpy(result, "501");
                        write(connectfd, result, sizeof(result));
                    }
                    else
                    {
                        printf("删除成功\n");
                        strcpy(result, "200");
                        write(connectfd, result, sizeof(result));
                    }
                }
            }
            else if (strcmp(buf, "exit") == 0) // 断开
            {
                printf("客户端指令--%s\n", buf);
                printf("同意断开\n");
                strcpy(result, "200");
                write(connectfd, result, sizeof(result));
                shutdown(connectfd, SHUT_RDWR); // 断开连接
                goto LINK;
            }
            connectfd = -1;
        }
    }
}

int select_callback(void *data, int argc, char **value, char **name)
{
    *(int *)data = 1;
    return 0;
}
